Event Handlers in SSIS

SQL server 2005 Integration services provide the ability to handle any type of event associated with the execution of its task and container (through the ability to configure corresponding handlers)

The following list contains more significant and commonly monitored types of events (you might be able to spot some of them in the Output window during package execution in Debug node)

• On Error:-

Generated as the result of on error condition. It falls into the category of the most frequently implemented types of event handler. Its purpose can be for additional information simplifying troubleshooting, or to notify about a problem and need for remediation.

• On Warning:-

Similar to the On error event, it is raised in response to a problem (although not as significant in terms of severity).

• On Information:-

Produces reporting information relating to the outcome of either validation or execution of a task or container (other than warning or error)

• On –Task Failed:-

Signals the failure of a task and typically follows on error event.

• On Pre Execute:-

Indicates that an exec table component is about to be launched.

• On Pre validate:-

Marks the beginning of the component validation stage, following the on pre execute event. The main purpose of validation is detection of potential problems that might prevent execution from completing successfully.

Inclined to build a profession as MSBI Developer? Then here is the blog post on, explore MSBI Training

• On post validate:-

Occurs as soon as the validation process of the component is completed ( following on prevail date event),

• On post-Execute:

Takes place after an executable component finishes running

• On variable value changed:

Allows you to detect changes to variables. The scope of the variable determines which executable will raise the event. In addition, in order for the event to take  place, the variable’s change event property must be set to true (the default is faces)

• On progress:

Raised at the point where measurable progress is made by the executable (for example, when running execute SQL Task).

• This can be evaluated by monitoring the values of the system variables associated with the On progress event handler, such as progress completes Progress count low, and progress count high. 

Steps to configure Event handler

Scenario:  Clean up or truncate the destination table before executing or loading data into destination.

Open Business Intelligence Development studio        ------------->

Create a new package and Rename it as Event Handler.dtsx       ------------->

In control flow drag and drop the data flow task and Rename it as DFT Event Handler       ------------->

In data flow drag and drop OLEDB source       ------------->

Double click on OLEDB source to edit it       ------------->

Provide connection manager if exists       ------------->

Select [human resource] . [Employee] table from the dropdown list       ------------->

Select columns from left panel       ------------->

MSBI Interview Questions

Click OK       ------------->

Drag and drop OLEDB destination and make a connection from OLEDB source to destination       ------------->

Double click on OLEDB destination       ------------->

Provide destination connection manager       ------------->

Click new to create a destination table and Rename OLEDB destination as employee details       ------------->

Click ok Twice       ------------->

Go to event handler tab       ------------->

Drag and Drop Execute SQL Task [on pre-validate Event handler for package executable]       ------------->

Double click on Execute SQL task provide connection if exists       ------------->

Provide the following SQL command. To clean up the data from destination table       ------------->

Truncate Table Employee details       ------------->

Click ok       ------------->

Execute package

For in-depth knowledge on MSBI click on:

• SQL Server Analysis Services
• Report Server
• Transfer SQL server Objects Task
• Flat file source
• Slowly Changing Dimension